package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Types;
import java.text.SimpleDateFormat;

//import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.security.userdetails.UserDetails;

import school.domain.Manager;

//import com.ibm.db2.jcc.a.SqlException;

public class JdbcManagerDao implements ManagerDao {

	@Override
	public UserDetails getByUsername(String username) throws Exception {
		Connection con = JdbcUtil.connect();
		Manager manager = new Manager();
		int rows;
		Date dateLeftManagers;

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Retrieve_Manager(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setString(1, username);
		prepCall.registerOutParameter(2, Types.VARCHAR);
		prepCall.registerOutParameter(3, Types.VARCHAR);
		prepCall.registerOutParameter(4, Types.VARCHAR);
		prepCall.registerOutParameter(5, Types.VARCHAR);
		prepCall.registerOutParameter(6, Types.VARCHAR);
		prepCall.registerOutParameter(7, Types.VARCHAR);
		prepCall.registerOutParameter(8, Types.VARCHAR);
		prepCall.registerOutParameter(9, Types.VARCHAR);
		prepCall.registerOutParameter(10, Types.DATE);
		prepCall.registerOutParameter(11, Types.DATE);
		prepCall.registerOutParameter(12, Types.DATE);
		prepCall.registerOutParameter(13, Types.VARCHAR);
		prepCall.registerOutParameter(14, Types.VARCHAR);
		prepCall.registerOutParameter(15, Types.INTEGER);
		prepCall.registerOutParameter(16, Types.INTEGER);
		try {
			prepCall.execute();
			rows = prepCall.getInt(16);

			if (rows == 1) {
				manager.setUsername(username);
				manager.setPassword(prepCall.getString(2));
				manager.setFirstName(prepCall.getString(3));
				manager.setLastName(prepCall.getString(4));
				manager.setStreet(prepCall.getString(5));
				manager.setBuildingNumber(prepCall.getString(6));
				manager.setApartmentNumber(prepCall.getString(7));
				manager.setCity(prepCall.getString(8));
				manager.setPostalCode(prepCall.getString(9));

				manager.setDateOfBirth(dateFormat.format(prepCall.getDate(10)));
				manager.setDateJoinedManagers(dateFormat.format(prepCall.getDate(11)));
				
				dateLeftManagers = prepCall.getDate(12);
				if (dateLeftManagers != null) {
					manager.setDateLeftManagers(dateFormat.format(dateLeftManagers));
				}

				manager.setEmail(prepCall.getString(13));
				manager.setPhoneNumber(prepCall.getString(14));
				manager.setId(prepCall.getInt(15));
				
				return manager;
			}
			else
				return null;
			
		/*} catch (SqlException e) {
			System.out.println(e.getErrorCode() + ", " + e.getSQLState());
			throw new DataRetrievalFailureException("DB Error!", e);*/
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

}
